var mysql = require("mysql");
var async = require('async');

var pool = mysql.createPool({
    host:"localhost",
    user:"root",
    password:"root",
    database:"moyu_game"
})//数据库连接配置

function query(sql,callback){
    pool.getConnection(function(err,connection){
        connection.query(sql, function (err,rows) {
            callback(err,rows)
            connection.release()
        })
    })
}//对数据库进行增删改查操作的基础


/**
 * 将数据插入到数据库
 * @param {Object} table
 * @param {Object} data
 * @param {Object} callback
 */
function insert(table, data, callback){
  let keyArr = new Array();
  let valueArr = new Array();
  
  for(let key in data){
    keyArr.push('`' + key + '`');
    valueArr.push(data[key]);
  }
  
  let sql = "INSERT INTO " + table +"(" + keyArr.join(',') + ") VALUES ('" + valueArr.join("', '") + "')";
  
  query(sql, callback);
}

/**
 * 自动创建查询条件
 * @param {Object|string} where
 */
function createWhereStr(where){
  var whereStr='';
  if(typeof(where) == 'string'){
    if(where.length > 0){
      whereStr =` WHERE ${where} `;
    }
    
  } else if(typeof(where) == 'object'){
    var whereArr = new Array();
    for(var key in where){
      whereArr.push("`" + key +"`='" + where[key] +"'");
    }
    
    if(whereArr.length > 0){
      whereStr = " WHERE " + whereArr.join(' AND ');
    }
  }
  return whereStr;
}

/**
 * 根据条件创建查询SQL
 * @param {Object} table
 * @param {Object} where
 * @param {Object} order
 * @param {Object} limit
 */
function createSelectSqlStr(table, field ,where, order, limit){
  var whereStr = '';
  var orderStr = '';
  var limitStr = '';
  
  whereStr = createWhereStr(where);
  
  if(order){
    orderStr = ` ORDER BY ${order}`;
  }
  if(limit){
    if(typeof(limit) == 'object'){
      limitStr = ` LIMIT ${limit.offset}, ${limit.limit}`;
    }
  }
  
  var sql = `SELECT ${field} FROM ${table} ${whereStr} ${orderStr} ${limitStr}`;
  var sqlCount = `SELECT count(*) as count FROM ${table} ${whereStr}`;
  
  return {sql: sql, sqlCount: sqlCount};
}

/**
 * 查询列表
 * @param {Object} table
 * @param {Object} field
 * @param {Object} where
 * @param {Object} order
 * @param {Object} limit
 * @param {Object} callback
 */
function select(table, field ,where, order, limit, callback){
  var sql = createSelectSqlStr(table, field ,where, order, limit);
  query(sql.sql, callback);
}

/**
 * 查询列表，并返回行数
 * @param {Object} table
 * @param {Object} field
 * @param {Object} where
 * @param {Object} order
 * @param {Object} limit
 * @param {Object} callback
 */
function selectAndCount(table, field ,where, order, limit, callback){
  var rList = new Array();
  var rCount = 0;
  var sql = createSelectSqlStr(table, field ,where, order, limit);
  
  async.parallel([function(cb){
    //获取集合
    query(sql.sql, function(err, rows){
      rList = rows;
      cb(null, rList);
    })
  }, function(cb){
    //获取行数
    query(sql.sqlCount, function(err, rows){
      rCount = rows[0]['count'];
      cb(null, rCount);
    })
  }], function(err, rs){
    //console.log(err, rs);
    callback(err, {list : rList, count : rCount});
  })
}

/**
 * 修改值
 * @param {Object} table
 * @param {Object} data
 * @param {Object} where
 * @param {Object} callback
 */
function update(table, data, where, callback){
  let dataArray = new Array();
  for(let key in data){
    dataArray.push("`" + key +"`='" + data[key] + "'");
  }
  
  let sql = "UPDATE " + table + " SET " + dataArray.join(',') + " WHERE " + where;
  
  query(sql, callback);
}

exports.query = query;
exports.insert = insert;
exports.update = update;
exports.select = select;
exports.selectAndCount = selectAndCount;